﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace System.Modules.SQL.Extensions
{
    /// <summary>
    /// 
    /// </summary>
    public static class SqlCommandExtensions
    {
        /// <summary>
        /// Use this method to add range of parameters
        /// </summary>
        /// <remarks>SQL Command must not be null</remarks>
        /// <param name="command"></param>
        /// <param name="sqlParameters"> </param>
        public static void AppendParameters(this SqlCommand command, params SqlParameter[] sqlParameters)
        {
            if (command == null) return;
            foreach (var sqlParameter in sqlParameters.Where(sqlParameter => !command.Parameters.Contains(sqlParameter.ParameterName)))
            {
                command.Parameters.Add(sqlParameter);
            }
        }

        /// <summary>
        /// Use this method to add range of parameters reflected from an Object.
        /// </summary>
        /// <remarks>SQL Command must not be null</remarks>
        /// <remarks>SQL Command Type must be CommandType.StoredProcedure</remarks>
        /// <remarks>SQL Command Text must be a stored procedure name</remarks>
        /// <param name="command"></param>
        /// <param name="sqlParameters"> </param>
        /// <param name="obj"></param>
        public static int BindParameters<TBaseObject>(this SqlCommand command, List<string> sqlParameters, object obj)
        {
            return command.BindParameters<TBaseObject>(sqlParameters, obj, false);
        }

        /// <summary>
        /// Use this method to add range of parameters reflected from an Object.
        /// </summary>
        /// <remarks>SQL Command must not be null</remarks>
        /// <remarks>SQL Command Type must be CommandType.StoredProcedure</remarks>
        /// <remarks>SQL Command Text must be a stored procedure name</remarks>
        /// <param name="command"></param>
        /// <param name="sqlParameters"> </param>
        /// <param name="obj"></param>
        /// <param name="appendTypeName"></param>
        private static int BindParameters<TBaseObject>(this SqlCommand command, ICollection<string> sqlParameters, object obj, bool appendTypeName)
        {
            if (command == null) return 0;
            if (command.CommandType != CommandType.StoredProcedure) return 0;
            if (obj != null)
            {
                var type = obj.GetType();
                var properties = type.GetProperties();
                foreach (var property in properties)
                {
                    var parameterNameBuilder = new StringBuilder();
                    if (appendTypeName)
                    {
                        parameterNameBuilder.Append(property.PropertyType.Name);
                    }
                    parameterNameBuilder.Append(property.Name);
                    var parameterName = parameterNameBuilder.ToString();
                    var value = property.GetValue(obj, null);
                    if (property.PropertyType.IsSubclassOf(typeof(TBaseObject)))
                    {
                        command.BindParameters<TBaseObject>(sqlParameters, value, true);
                    }
                    else
                    {
                        if (sqlParameters.Contains(parameterName))
                        {
                            command.AppendParameters(new SqlParameter { ParameterName = parameterName, Value = value ?? DBNull.Value });
                            //var parameter = new SqlParameter { ParameterName = parameterName, Value = value ?? DBNull.Value };
                            //if (!command.Parameters.Contains(parameterName))
                            //{
                            //    command.Parameters.Add(parameter);
                            //}
                        }
                    }
                }
            }
            return command.Parameters.Count;
        }

        /// <summary>
        /// Use this method to bind SQL Command to get all stored procedure parameters
        /// </summary>
        /// <param name="command"></param>
        /// <param name="storedProcedureName">Without Schema</param>
        /// <param name="schema">
        /// <example>
        /// [dbo]
        /// </example>
        /// </param>
        internal static void GetStoredProcedureParametersCommand(this SqlCommand command, string storedProcedureName, string schema = "[dbo]")
        {
            if (command == null) command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = string.Format("select c.name from  syscolumns c INNER JOIN sys.objects o on o.object_id = c.id INNER JOIN sys.schemas s on s.schema_id = o.schema_id where o.name = {0} AND s.name = {1}", storedProcedureName, schema);
        }
    }
}
